package com.rickpan.repository;

import com.rickpan.entity.FriendRelationship;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * 好友关系数据访问层
 */
@Repository
public interface FriendRelationshipRepository extends JpaRepository<FriendRelationship, Long> {

    /**
     * 查找用户的所有好友关系
     */
    List<FriendRelationship> findByUserIdAndStatus(Long userId, FriendRelationship.Status status);

    /**
     * 查找用户的所有好友关系（分页）
     */
    Page<FriendRelationship> findByUserIdAndStatus(Long userId, FriendRelationship.Status status, Pageable pageable);

    /**
     * 查找用户指定分组的好友
     */
    List<FriendRelationship> findByUserIdAndGroupNameAndStatus(Long userId, String groupName, FriendRelationship.Status status);

    /**
     * 查找用户指定分组的好友（分页）
     */
    Page<FriendRelationship> findByUserIdAndGroupNameAndStatus(Long userId, String groupName, FriendRelationship.Status status, Pageable pageable);

    /**
     * 查找用户的所有好友分组
     */
    @Query("SELECT DISTINCT fr.groupName FROM FriendRelationship fr WHERE fr.userId = :userId AND fr.status = :status")
    List<String> findDistinctGroupNamesByUserIdAndStatus(@Param("userId") Long userId, @Param("status") FriendRelationship.Status status);

    /**
     * 检查两个用户是否为好友
     */
    Optional<FriendRelationship> findByUserIdAndFriendIdAndStatus(Long userId, Long friendId, FriendRelationship.Status status);

    /**
     * 检查两个用户之间是否存在好友关系（双向检查）
     */
    @Query("SELECT fr FROM FriendRelationship fr WHERE " +
           "((fr.userId = :userId AND fr.friendId = :friendId) OR " +
           "(fr.userId = :friendId AND fr.friendId = :userId)) AND " +
           "fr.status = :status")
    List<FriendRelationship> findBidirectionalFriendship(@Param("userId") Long userId, 
                                                         @Param("friendId") Long friendId, 
                                                         @Param("status") FriendRelationship.Status status);

    /**
     * 统计用户的好友数量
     */
    long countByUserIdAndStatus(Long userId, FriendRelationship.Status status);

    /**
     * 根据关键词搜索用户的好友
     */
    @Query("SELECT fr FROM FriendRelationship fr " +
           "JOIN fr.friend f " +
           "WHERE fr.userId = :userId AND fr.status = :status AND " +
           "(LOWER(f.username) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.realName) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(fr.remark) LIKE LOWER(CONCAT('%', :keyword, '%')))")
    List<FriendRelationship> searchFriends(@Param("userId") Long userId, 
                                          @Param("keyword") String keyword, 
                                          @Param("status") FriendRelationship.Status status);

    /**
     * 获取用户的在线好友
     */
    @Query("SELECT fr FROM FriendRelationship fr " +
           "JOIN UserOnlineStatus uos ON fr.friendId = uos.userId " +
           "WHERE fr.userId = :userId AND fr.status = :status AND uos.status = 'ONLINE'")
    List<FriendRelationship> findOnlineFriends(@Param("userId") Long userId, 
                                              @Param("status") FriendRelationship.Status status);

    /**
     * 获取用户好友的在线状态统计
     */
    @Query("SELECT uos.status, COUNT(uos.status) FROM FriendRelationship fr " +
           "JOIN UserOnlineStatus uos ON fr.friendId = uos.userId " +
           "WHERE fr.userId = :userId AND fr.status = :status " +
           "GROUP BY uos.status")
    List<Object[]> getFriendsOnlineStatusStats(@Param("userId") Long userId, 
                                              @Param("status") FriendRelationship.Status status);

    /**
     * 删除双向好友关系
     */
    void deleteByUserIdAndFriendId(Long userId, Long friendId);

    /**
     * 批量删除用户的所有好友关系
     */
    void deleteByUserId(Long userId);

    /**
     * 查找互相为好友的关系
     */
    @Query("SELECT fr1 FROM FriendRelationship fr1 " +
           "WHERE EXISTS (SELECT fr2 FROM FriendRelationship fr2 " +
           "WHERE fr1.userId = fr2.friendId AND fr1.friendId = fr2.userId " +
           "AND fr1.status = :status AND fr2.status = :status) " +
           "AND fr1.userId = :userId")
    List<FriendRelationship> findMutualFriends(@Param("userId") Long userId, 
                                              @Param("status") FriendRelationship.Status status);

    /**
     * 检查是否存在好友关系（不区分方向）
     */
    @Query("SELECT CASE WHEN COUNT(fr) > 0 THEN true ELSE false END FROM FriendRelationship fr " +
           "WHERE ((fr.userId = :userId AND fr.friendId = :friendId) OR " +
           "(fr.userId = :friendId AND fr.friendId = :userId)) AND " +
           "fr.status = :status")
    boolean existsFriendship(@Param("userId") Long userId,
                            @Param("friendId") Long friendId,
                            @Param("status") FriendRelationship.Status status);

    /**
     * 统计指定分组下的好友数量
     */
    long countByUserIdAndGroupNameAndStatus(Long userId, String groupName, FriendRelationship.Status status);

    /**
     * 批量更新分组名称
     */
    @Modifying
    @Query("UPDATE FriendRelationship fr SET fr.groupName = :newGroupName " +
           "WHERE fr.userId = :userId AND fr.groupName = :oldGroupName")
    int updateGroupNameByUserIdAndGroupName(@Param("userId") Long userId,
                                           @Param("oldGroupName") String oldGroupName,
                                           @Param("newGroupName") String newGroupName);
}
